课程3：查询复杂的数据类型

课程3：查询复杂的数据类型

目标

本节课的重点是关于自定义数据和复杂数据类型的练习和运算符的查询。Drill 提供了直观的 SQL 扩展到这样的数据当中，并提高高性能的查询和复杂构建复杂的数据。

查询

现在你可以运行 SQL 语句去查询不同的表和文件，你可以尝试去查询一些复杂的数据类型。

进入到目录或子目录中的文件，使用单挑 SELECT 语句。
展示简单的方式去访问 JSON 文件中复杂的数据。
展示 repeated_count 函数去聚合数组中的值。

查询分区目录

你可以在 Drill 中使用特定的变量，指向你工作路径的子目录：

dir0
dir1
…

请注意，这些变量是基于文件系统的分区动态确定的。在没有前定义的分区上存在。下面是一个可视化的例子，展示这个流程是如何工作的：

切换到 dfs.log 工作区间：

0: jdbc:drill:> use dfs.logs;
+-------+---------------------------------------+
|  ok   |                summary                |
+-------+---------------------------------------+
| true  | Default schema changed to [dfs.logs]  |
+-------+---------------------------------------+
1 row selected

查询指定年数据：

0: jdbc:drill:> select * from logs where dir0='2013' limit 10;
+-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
| dir0  | dir1  | trans_id  |    date     |   time    | cust_id  | device  | state  | camp_id  | keywords  | prod_id  | purch_flag  |
+-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
| 2013  | 8     | 12104     | 08/29/2013  | 09:34:37  | 962      | IOS5    | ma     | 3        | milhouse  | 17       | false       |
| 2013  | 8     | 12132     | 08/23/2013  | 01:11:25  | 4        | IOS7    | mi     | 11       | hi        | 439      | false       |
| 2013  | 8     | 12177     | 08/14/2013  | 13:48:50  | 23       | AOS4.2  | il     | 14       | give      | 382      | false       |
| 2013  | 8     | 12180     | 08/03/2013  | 20:48:45  | 1509     | IOS7    | ca     | 0        | i'm       | 340      | false       |
| 2013  | 8     | 12187     | 08/16/2013  | 10:28:07  | 0        | IOS5    | ny     | 16       | clicking  | 11       | false       |
| 2013  | 8     | 12190     | 08/10/2013  | 14:16:50  | 9        | IOS5    | va     | 3        | a         | 495      | false       |
| 2013  | 8     | 12200     | 08/02/2013  | 20:54:38  | 42219    | IOS5    | ia     | 0        | what's    | 346      | false       |
| 2013  | 8     | 12210     | 08/05/2013  | 20:12:24  | 8073     | IOS5    | sc     | 5        | if        | 33       | false       |
| 2013  | 8     | 12235     | 08/28/2013  | 07:49:45  | 595      | IOS5    | tx     | 2        | that      | 51       | false       |
| 2013  | 8     | 12239     | 08/13/2013  | 03:24:31  | 2        | IOS5    | or     | 6        | haw-haw   | 40       | false       |
+-------+-------+-----------+-------------+-----------+----------+---------+--------+----------+-----------+----------+-------------+
10 rows selected

该查询在约束条件是子目录限制在 2013。变量是从 dir0 指向下一级的 dir1，等等。因此，此查询的返回结果是返回 2013 年的 10 条记录。

在查询中使用多重判断查询

此查询返回通过 2013 年八月购买 iOS5 设备的客户 ID 列表：

0: jdbc:drill:> select dir0 as yr, dir1 as mth, cust_id from logs
where dir0='2013' and dir1='8' and device='IOS5' and purch_flag='true'
order by `date`;
+-------+------+----------+
|  yr   | mth  | cust_id  |
+-------+------+----------+
| 2013  | 8    | 4        |
| 2013  | 8    | 521      |
| 2013  | 8    | 1        |
| 2013  | 8    | 2        |
...

统计每个用户的每月

0: jdbc:drill:> select cust_id, dir1 month_no, count(*) month_count from logs
where dir0=2014 group by cust_id, dir1 order by cust_id, month_no limit 10;
+----------+-----------+--------------+
| cust_id  | month_no  | month_count  |
+----------+-----------+--------------+
| 0        | 1         | 143          |
| 0        | 2         | 118          |
| 0        | 3         | 117          |
| 0        | 4         | 115          |
| 0        | 5         | 137          |
| 0        | 6         | 117          |
| 0        | 7         | 142          |
| 0        | 8         | 19           |
| 1        | 1         | 66           |
| 1        | 2         | 59           |
+----------+-----------+--------------+
10 rows selected

该分组查询使用了聚合函数，分组条件由客户 ID 和月份组成，约束条件为年份：2014。

查询复杂数据

Drill 提供了一些专门的运算符和函数，能够让你使用嵌套数据进行分析。如果你熟悉 JavaScript 语法，你将会知道如何去扩展该工作。

切换到dfs.clicks：

0: jdbc:drill:> use dfs.clicks;
+-------+-----------------------------------------+
|  ok   |                 summary                 |
+-------+-----------------------------------------+
| true  | Default schema changed to [dfs.clicks]  |
+-------+-----------------------------------------+
1 row selected

研究点击数据

值得注意的是，user_info 和 trans_info 列包含嵌套数据：数组中包含数组。下面展示如何查询复杂的数据。

0: jdbc:drill:> select * from `clicks/clicks.json` limit 5;
+-----------+-------------+-----------+---------------------------------------------------+---------------------------------------------------------------------------+
| trans_id  |    date     |   time    |                     user_info                     |                                trans_info                                 |
+-----------+-------------+-----------+---------------------------------------------------+---------------------------------------------------------------------------+
| 31920     | 2014-04-26  | 12:17:12  | {"cust_id":22526,"device":"IOS5","state":"il"}    | {"prod_id":[174,2],"purch_flag":"false"}                                  |
| 31026     | 2014-04-20  | 13:50:29  | {"cust_id":16368,"device":"AOS4.2","state":"nc"}  | {"prod_id":[],"purch_flag":"false"}                                       |
| 33848     | 2014-04-10  | 04:44:42  | {"cust_id":21449,"device":"IOS6","state":"oh"}    | {"prod_id":[582],"purch_flag":"false"}                                    |
| 32383     | 2014-04-18  | 06:27:47  | {"cust_id":20323,"device":"IOS5","state":"oh"}    | {"prod_id":[710,47],"purch_flag":"false"}                                 |
| 32359     | 2014-04-19  | 23:13:25  | {"cust_id":15360,"device":"IOS5","state":"ca"}    | {"prod_id":[0,8,170,173,1,124,46,764,30,711,0,3,25],"purch_flag":"true"}  |
+-----------+-------------+-----------+---------------------------------------------------+---------------------------------------------------------------------------+
5 rows selected

分解 user_info 列：

0: jdbc:drill:> select t.user_info.cust_id as custid, t.user_info.device as device,
t.user_info.state as state
from `clicks/clicks.json` t limit 5;
+---------+---------+--------+
| custid  | device  | state  |
+---------+---------+--------+
| 22526   | IOS5    | il     |
| 16368   | AOS4.2  | nc     |
| 21449   | IOS6    | oh     |
| 20323   | IOS5    | oh     |
| 15360   | IOS5    | ca     |
+---------+---------+--------+
5 rows selected (0.171 seconds)

该查询使用简单的表.列.列 这样的方式去分解嵌套数据。如下所示：

t.user_info.cust_id

在查询中，这里的 t 指的是表的别名，user_info 是最外层的列，并且 cust_id 是一个嵌套列。

表别名是必需的；否则列名，例如 user_info 会在 SQL 中解析的时候被当作成表名。

解析 trans_info 列：

0: jdbc:drill:> select t.trans_info.prod_id as prodid, t.trans_info.purch_flag as
purchased
from `clicks/clicks.json` t limit 5;
+-------------------------------------------+------------+
|                  prodid                   | purchased  |
+-------------------------------------------+------------+
| [174,2]                                   | false      |
| []                                        | false      |
| [582]                                     | false      |
| [710,47]                                  | false      |
| [0,8,170,173,1,124,46,764,30,711,0,3,25]  | true       |
+-------------------------------------------+------------+
5 rows selected

注意，这个结果表明，prod_id 列包含一个 ID 数组（一个或多个产品 ID于每行，用逗号分割）。下一步将为大家演示如何去访问这种数据。

数组查询

现在使用 [n] 符号，其中 n 是在数组中的位置，从 0（不是 1）表示第一个值。你可以使用这个符号写一些有趣的查询，去查询一些嵌套数组数据。

例如：

trans_info.prod_id[0]

获取指定嵌套数据中的值，在值存在的情况下，获取第 21 个值：

trans_info.prod_id[20]

获取第一个产品的值：

0: jdbc:drill:> select t.trans_id, t.trans_info.prod_id[0] from `clicks/clicks.json` t limit 5;
+------------+------------+
|  trans_id  |   EXPR$1   |
+------------+------------+
| 31920      | 174        |
| 31026      | null       |
| 33848      | 582        |
| 32383      | 710        |
| 32359      | 0          |
+------------+------------+
5 rows selected

查询的记录当中至少包含 21 条记录

0: jdbc:drill:> select t.trans_id, t.trans_info.prod_id[20]
from `clicks/clicks.json` t
where t.trans_info.prod_id[20] is not null
order by trans_id limit 5;
+------------+------------+
|  trans_id  |   EXPR$1   |
+------------+------------+
| 10328      | 0          |
| 10380      | 23         |
| 10701      | 1          |
| 11100      | 0          |
| 11219      | 46         |
+------------+------------+
5 rows selected

该查询返回一个 transaction 的 ID 和 product 的 ID 的记录，其中 product 的 ID 不为 null，在数组的下标为 21 的索引处开始。

返回点击商品区间

0: jdbc:drill:> select * from (select t.trans_id, t.trans_info.prod_id[0] as prodid,
t.trans_info.purch_flag as purchased
from `clicks/clicks.json` t) sq
where sq.prodid between 700 and 750 and sq.purchased='true'
order by sq.prodid;
+------------+------------+------------+
|  trans_id  |   prodid   | purchased  |
+------------+------------+------------+
| 21886      | 704        | true       |
| 20674      | 708        | true       |
| 22158      | 709        | true       |
| 34089      | 714        | true       |
| 22545      | 714        | true       |
| 37500      | 717        | true       |
| 36595      | 718        | true       |
...

此查询是按照商品的 ID 进行排序的。

操作数组

在每个 session 中，排名成功的点击切换和统计商品的搜索：

0: jdbc:drill:> select t.trans_id, t.`date` as session_date, t.user_info.cust_id as
cust_id, t.user_info.device as device, repeated_count(t.trans_info.prod_id) as
prod_count, t.trans_info.purch_flag as purch_flag
from `clicks/clicks.json` t
where t.trans_info.purch_flag = 'true' order by prod_count desc;
+------------+--------------+------------+------------+------------+------------+
|  trans_id  | session_date |  cust_id   |   device   | prod_count | purch_flag |
+------------+--------------+------------+------------+------------+------------+
| 37426      | 2014-04-06   | 18709      | IOS5       | 34         | true       |
| 31589      | 2014-04-16   | 18576      | IOS6       | 31         | true       |
| 11600      | 2014-04-07   | 4260       | AOS4.2     | 28         | true       |
| 35074      | 2014-04-03   | 16697      | AOS4.3     | 27         | true       |
| 17192      | 2014-04-22   | 2501       | AOS4.2     | 26         | true       |
...

该查询使用了一个 SQL 的扩展功能，repeated_count 函数，得到一个数组的聚合统计。查询返回购买的每一个会话中搜索的产品的数量，并使用降序功能，进行排列计数。仅仅针对购买的点击数统计。

存储结果集到表中，以便复用和分析

为了便于在该结果集上进行分析，可以方便快捷的从查询结果中创建一个 Drill 表。

继续使用 dfs.clicks 工作区间

0: jdbc:drill:> use dfs.clicks;
+-------+-----------------------------------------+
|  ok   |                 summary                 |
+-------+-----------------------------------------+
| true  | Default schema changed to [dfs.clicks]  |
+-------+-----------------------------------------+
1 row selected (1.61 seconds)

返回商品搜索结果

0: jdbc:drill:> select o.cust_id, o.order_total, t.trans_info.prod_id[0] as prod_id
from
hive.orders as o
join `clicks/clicks.json` t
on o.cust_id=t.user_info.cust_id
where o.order_total > (select avg(inord.order_total)
                      from hive.orders inord
                      where inord.state = o.state);
+----------+--------------+----------+
| cust_id  | order_total  | prod_id  |
+----------+--------------+----------+
| 1328     | 73           | 26       |
| 1328     | 146          | 26       |
| 1328     | 56           | 26       |
| 1328     | 91           | 26       |
| 1328     | 74           | 26       |
...
+----------+--------------+----------+
107,482 rows selected (14.863 seconds)

这个查询返回一个正在搜索的产品列表，这些产品价格是高于平均水平的。

实现预览结果

0: jdbc:drill:> create table product_search as select o.cust_id, o.order_total, t.trans_info.prod_id[0] as prod_id
from
hive.orders as o
join `clicks/clicks.json` t
on o.cust_id=t.user_info.cust_id
where o.order_total > (select avg(inord.order_total)
                      from hive.orders inord
                      where inord.state = o.state);
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 107482                     |
+-----------+----------------------------+
1 row selected (3.488 seconds)

这个例子使用了一个 CTAS 语法创建一个表，基于相关子查询。该表包含所有的查询返回的行数（107482），并将它们存储在存储插件指定的格式（该例子使用的是 Parquet 格式）。你可以创建表，并以 CSV，Parquet 和 JSON 格式存储数据。

统计一个新表的行数

该例子简单的检验 CTAS 语法的正确性。

0: jdbc:drill:> select count(*) from product_search;
+---------+
| EXPR$0  |
+---------+
| 107482  |
+---------+
1 row selected (0.155 seconds)

找出表存储文件：

[root@maprdemo product_search]# cd /mapr/demo.mapr.com/data/nested/product_search
[root@maprdemo product_search]# ls -la
total 451
drwxr-xr-x. 2 mapr mapr      1 Sep 15 13:41 .
drwxr-xr-x. 4 root root      2 Sep 15 13:41 ..
-rwxr-xr-x. 1 mapr mapr 460715 Sep 15 13:41 0_0_0.parquet

值得注意的是，表存储在一个叫 0_0_0.parquet 的文件当中。该文件存储在本地，由 dfs.clicks 工作空间定义。

"location": "http://demo.mapr.com/data/nested"

在你创建的表中，有一个具有相同名称的子目录。

6.课程3：查询复杂的数据类型